package com.etc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.etc.dao.BroadcastingStationDao;
import com.etc.entity.Album;
import com.etc.entity.BroadcastingStation;
import com.etc.entity.BroadcastingStationDetails;
import com.etc.entity.Languages;
import com.etc.entity.MusicInfo;
import com.etc.entity.MusicStyle;
import com.etc.entity.MusicVideo;
import com.etc.entity.Singger;
import com.etc.util.DBUtil;

import jdk.nashorn.internal.objects.annotations.Where;

public class BroadcastingStationDaoImpl implements BroadcastingStationDao{

	//根据条件查询电台歌单表
		@Override
		public List<BroadcastingStation> find(String zt) {
			List<BroadcastingStation> list = new ArrayList<BroadcastingStation>();
			StringBuilder sql = new StringBuilder();
			sql.append("select broadcasting_station_id,")
			   .append(" broadcasting_station_name,")
			   .append(" broadcasting_station_img_path,")
			   .append(" play_number")
			   .append(" from broadcasting_station");
			   
			  			  
			  if ("热门".equals(zt)) {
			  sql.append(" order by play_number");
			  }else if("风格".equals(zt)) { 
				  sql.append(" where style_id is not null order by play_number");
			  }else if ("心情".equals(zt)) {
				  sql.append(" where mood_id is not null order by play_number"); 
			  }else if("场景".equals(zt)) {   
				  sql.append(" where scene_id is not null order by play_number");
			  }else if ("主题".equals(zt)) {
				  sql.append(" where theme_id is not null order by play_number");
			  }
			   sql.append(" limit 0,6");
			ResultSet rs = DBUtil.query(sql.toString());
					
			try {
				while (rs.next()) {
					Integer broadcastingStationId = rs.getInt("broadcasting_station_id");
					String broadcastingStationImgPath = rs.getString("broadcasting_station_img_path");
					String broadcastingStationName = rs.getString("broadcasting_station_name");
					Integer playNumber = rs.getInt("play_number");
					//创建对象
					BroadcastingStation broadcastingStation = new BroadcastingStation();
					broadcastingStation.setBroadcastingStationId(broadcastingStationId);
					broadcastingStation.setBroadcastingStationName(broadcastingStationName);
					broadcastingStation.setBroadcastingStationImgPath(broadcastingStationImgPath);
					broadcastingStation.setPlayNumber(playNumber);
					list.add(broadcastingStation);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;
		}

	
	/**
	 * 根据组合条件查歌单及分页
	 */
	@Override
	public List<BroadcastingStation> findByConditions(String languagesName, String styleName, String themeName,
			String moodName, String sceneName,int pageNum,int pageSize) {
		List<BroadcastingStation> listBroadcastingStation = new ArrayList<BroadcastingStation>();
		StringBuilder sb = new StringBuilder();
		sb.append("select A.broadcasting_station_id,")
		.append(" A.broadcasting_station_name,")
		.append(" A.broadcasting_station_img_path,")
		.append(" A.play_number")
		.append(" from broadcasting_station A")
		.append(" left join languages B on A.languages_id=B.languages_id")
		.append(" left join theme C on A.theme_id=C.theme_id")
		.append(" left join mood D on A.mood_id=D.mood_id")
		.append(" left join music_style E on A.style_id=E.style_id")
		.append(" left join scene F on A.scene_id=F.scene_id");
	
		
		// 动态SQL
				sb.append(" where 1 = 1");
				
				// 判断参数情况
				if ( languagesName!= null && !"".equals(languagesName)) {
					sb.append(" and languages_name ='" + languagesName + "'");
				}
				if (styleName!= null && !"".equals(styleName)) {
					sb.append(" and style_name ='" + styleName + "'");
				}
				if (themeName!= null && !"".equals(themeName)) {
					
					sb.append(" and  theme_name ='" + themeName + "'");
				}
				if (moodName!= null && !"".equals(moodName)) {
					sb.append(" and mood_name ='" + moodName + "'");
				}
				if (sceneName!= null && !"".equals(sceneName)) {
					sb.append(" and scene_name ='" + sceneName + "'");
				}
				// 进行分页  select * from 表  where... limit a,b;
				sb.append(" limit " +  (pageNum - 1) * pageSize + "," + pageSize);

		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				
				//获取数据
				Integer  broadcastingStationId  =  rs.getInt("broadcasting_station_id");
				String  broadcastingStationName = rs.getString("broadcasting_station_name");
				String  broadcastingStationImgPath = rs.getString("broadcasting_station_img_path");
				Integer  playNumber  =  rs.getInt("play_number");
				//构建歌单对象
				BroadcastingStation broadcastingStation=new BroadcastingStation();
				broadcastingStation.setBroadcastingStationId(broadcastingStationId);
				broadcastingStation.setBroadcastingStationName(broadcastingStationName);
				broadcastingStation.setBroadcastingStationImgPath(broadcastingStationImgPath);
				broadcastingStation.setPlayNumber(playNumber);
				//集合
				listBroadcastingStation.add(broadcastingStation);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return listBroadcastingStation;
	}

	/**
	 * 根据组合条件查歌单总数
	 */
	@Override
	public int countByCondition(String languagesName, String styleName, String themeName, String moodName,
			String sceneName) {
		int count = 0;
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" count(*)")
		.append(" from broadcasting_station A")
		.append(" inner join languages B on A.languages_id=B.languages_id")
		.append(" inner join theme C on A.theme_id=C.theme_id")
		.append(" inner join mood D on A.mood_id=D.mood_id")
		.append(" inner join music_style E on A.style_id=E.style_id")
		.append(" inner join scene F on A.scene_id=F.scene_id");
	
		
		// 动态SQL
				sb.append(" where 1 = 1");
				
				// 判断参数情况
				if ( languagesName!= null && !"".equals(languagesName)) {
					sb.append(" and languages_name ='" + languagesName + "'");
				}
				if (styleName!= null && !"".equals(styleName)) {
					sb.append(" and style_name ='" + styleName + "'");
				}
				if (themeName!= null && !"".equals(themeName)) {
					
					sb.append(" and  theme_name ='" + themeName + "'");
				}
				if (moodName!= null && !"".equals(moodName)) {
					sb.append(" and mood_name ='" + moodName + "'");
				}
				if (sceneName!= null && !"".equals(sceneName)) {
					sb.append(" and scene_name ='" + sceneName + "'");
				}
				

		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				
				//获取数据
				count =  rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}


	@Override
	public int findFirstByBroadcastingStation(int BroadcastingStationId) {
		//获取电台歌单详情的第一首歌的ID
		int musicId = 0;
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" music_id")
		.append(" from broadcasting_station_details ")
		.append(" where broadcasting_station_id = ? ");
		
		ResultSet rs = DBUtil.query(sb.toString(), BroadcastingStationId);
		try {
			while (rs.next()) {
				 musicId = rs.getInt(1);
				 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return musicId;
	}
	@Override
	public BroadcastingStation findBroadcastingStationById(int broadcastingStationId, List<MusicInfo> musicInfoList) {
		BroadcastingStation broadcastingStation=new BroadcastingStation();
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" A.broadcasting_station_name,")
		.append(" A.broadcasting_station_img_path,")
		.append(" A.play_number,")
		.append(" B.languages_name,")
		.append(" C.style_name")
		.append(" from broadcasting_station A")
		.append(" left join languages B on A.languages_id=B.languages_id")
		.append(" left join music_style C on A.style_id=C.style_id");
		
		
		// 动态SQL
				sb.append(" where A.broadcasting_station_id="+broadcastingStationId);
				ResultSet rs = DBUtil.query(sb.toString());
				try {
					while (rs.next()) {
						String broadcastingStationName = rs.getString("broadcasting_station_name");
						String broadcastingStationImg = rs.getString("broadcasting_station_img_path");
						Integer playNumber = rs.getInt("play_number");
						String languagesName = rs.getString("languages_name");
						Languages languages = new Languages();
						languages.setLanguagesName(languagesName);
						
						String styleName = rs.getString("style_name");
						MusicStyle musicStyle = new MusicStyle();
						musicStyle.setStyleName(styleName);
						// 歌单对象
						broadcastingStation.setBroadcastingStationName(broadcastingStationName);
						broadcastingStation.setBroadcastingStationImgPath(broadcastingStationImg);
						broadcastingStation.setPlayNumber(playNumber);
						broadcastingStation.setLanguages(languages);
						broadcastingStation.setMusicStyle(musicStyle);
						broadcastingStation.setMusicInfoList(musicInfoList);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
		return broadcastingStation;
	}


	@Override
	public List<BroadcastingStation> findByUserId(Integer userId) {
		List<BroadcastingStation> broadcastingStations = new ArrayList<BroadcastingStation>();
		StringBuilder sb = new StringBuilder();
		sb.append("select B.broadcasting_station_id,")
		.append(" B.broadcasting_station_name,")
		.append(" B.broadcasting_station_img_path,")
		.append(" B.play_number")
		.append(" from song_sheet_collection A")
		.append(" inner join broadcasting_station B")
		.append(" on A.broadcasting_station_id = B.broadcasting_station_id")
		.append(" where A.user_id = ?");
		
		ResultSet rs = DBUtil.query(sb.toString(), userId);
		try {
			while (rs.next()) {
				BroadcastingStation broadcastingStation = new BroadcastingStation();
				broadcastingStation.setBroadcastingStationId(rs.getInt("broadcasting_station_id"));
				broadcastingStation.setBroadcastingStationName(rs.getString("broadcasting_station_name"));
				broadcastingStation.setBroadcastingStationImgPath(rs.getString("broadcasting_station_img_path"));
				broadcastingStation.setPlayNumber(rs.getInt("play_number"));
				broadcastingStations.add(broadcastingStation);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return broadcastingStations;
	}
	
}
